﻿using Entity;
using IRealize;
using Microsoft.Data.SqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;  

namespace Realize
{
    public class RLXLK : IRLXLK   
    {
        public async Task<int> AddHF(Human_File human_File, string sqlserver)
        {
            using (SqlConnection connection = new SqlConnection(sqlserver))
            {
                await connection.OpenAsync();

                // 1. 固定2位前缀  
                string prefix = "bt";

                // 2. 时间戳部分（精确到秒，14位：yyyyMMddHHmmss）
                string timestamp = DateTime.Now.ToString("yyyyMMddHHmmss");

                // 3. 生成4位随机数字（确保0000-9999）
                int randomNumber = Random.Shared.Next(0, 10000);
                string randomPart = randomNumber.ToString("D4"); // 不足4位补前导零

                // 4. 组合生成20位编号
                human_File.human_id =$"{prefix}{timestamp}{randomPart}";

                // 执行插入操作
                string sql = @"
            INSERT INTO [dbo].[human_file] (
    [human_id], [first_kind_id], [first_kind_name], 
    [second_kind_id], [second_kind_name], [third_kind_id], [third_kind_name],
     [human_major_kind_id],[human_major_kind_name],  [human_major_id], [hunma_major_name], [human_pro_designation],
    [human_name], [human_sex], [human_email], [human_telephone], 
    [human_qq], [human_mobilephone], [human_address], [human_postcode], 
    [human_nationality], [human_birthplace], [human_birthday], [human_race],
    [human_religion], [human_party], [human_id_card], [human_society_security_id],
    [human_age], [human_educated_degree], [human_educated_years], 
    [human_educated_major], [salary_standard_id],[salary_standard_name], [human_bank], [human_account],
     [check_status],[register], [regist_time], [human_speciality], [human_hobby], 
    [remark], [human_histroy_records], [human_family_membership],[human_picture],[salary_sum],[demand_salaray_sum],[paid_salary_sum]
)
VALUES (
    @human_id, @first_kind_id, @first_kind_name, 
    @second_kind_id, @second_kind_name, @third_kind_id, @third_kind_name,
    @human_major_kind_id,@human_major_kind_name, @human_major_id, @hunma_major_name, @human_pro_designation,
    @human_name, @human_sex, @human_email, @human_telephone, 
    @human_qq, @human_mobilephone, @human_address, @human_postcode, 
    @human_nationality, @human_birthplace, @human_birthday, @human_race,
    @human_religion, @human_party, @human_id_card, @human_society_security_id,
    @human_age, @human_educated_degree, @human_educated_years, 
    @human_educated_major, @salary_standard_id,@salary_standard_name, @human_bank, @human_account,
    @check_status,@register, @regist_time, @human_speciality, @human_hobby, 
    @remark, @human_histroy_records, @human_family_membership,@human_picture,@salary_sum,@demand_salaray_sum,@paid_salary_sum
)";



                // 使用动态参数对象，确保属性名正确
                var parameters = new
                {
                    human_File.human_id,
                    human_File.first_kind_id,
                    human_File.first_kind_name,
                    human_File.second_kind_id,
                    human_File.second_kind_name,
                    human_File.third_kind_id,
                    human_File.third_kind_name,
                    human_File.human_major_kind_id,
                    human_File.human_major_kind_name,
                    human_File.human_major_id,
                    human_File.hunma_major_name,
                    human_File.human_pro_designation,
                    human_File.human_name,
                    human_File.human_sex,
                    human_File.human_email,
                    human_File.human_telephone,
                    human_File.human_qq,
                    human_File.human_mobilephone,
                    human_File.human_address,
                    human_File.human_postcode,
                    human_File.human_nationality,
                    human_File.human_birthplace,
                    human_File.human_birthday,
                    human_File.human_race,
                    human_File.human_religion,
                    human_File.human_party,
                    human_File.human_id_card,
                    human_File.human_society_security_id,
                    human_File.human_age,
                    human_File.human_educated_degree,
                    human_File.human_educated_years,
                    human_File.human_educated_major,
                    human_File.salary_standard_id,
                    human_File.salary_standard_name,
                    human_File.human_bank,
                    human_File.human_account,
                    human_File.check_status,
                    human_File.register,
                    human_File.regist_time,
                    human_File.human_speciality,
                    human_File.human_hobby,
                    human_File.remark,
                    human_File.human_histroy_records,
                    human_File.human_family_membership,
                    human_File.human_picture,
                    human_File.salary_sum,
                    human_File.demand_salaray_sum,
                    human_File.paid_salary_sum,
                  
                };


                return await connection.ExecuteAsync(sql, parameters);



            }
        }

        public async Task<List<Salary_Standard>> CXXCXLK(string sqlserver)
        {
            using (var sqlConnection = new SqlConnection(sqlserver))
            {
                await sqlConnection.OpenAsync(); // 异步打开连接

                string sql = " select * from [dbo].[salary_standard] where check_status = 1 or change_status = 1";

                try
                {
                    // 使用异步查询方法
                    var result = await sqlConnection.QueryAsync<Salary_Standard>(sql);
                    return result.AsList(); // 转换为List
                }
                catch (SqlException ex)
                {
                    // 处理SQL异常（如连接失败、查询错误）
                    Console.WriteLine($"数据库错误：{ex.Message}");
                    throw; // 可选择重新抛出或包装异常
                }
                catch (Exception ex)
                {
                    // 处理其他异常
                    Console.WriteLine($"意外错误：{ex.Message}");
                    throw;
                }
            }
        }

        public async Task<List<Human_File>> CXXLK(string sqlserver)
        {
            using (var sqlConnection = new SqlConnection(sqlserver))
            {
                await sqlConnection.OpenAsync(); // 异步打开连接

                string sql = "SELECT TOP 2 * FROM [dbo].[human_file]";

                try
                {
                    // 使用异步查询方法
                    var result = await sqlConnection.QueryAsync<Human_File>(sql);
                    return result.AsList(); // 转换为List
                }
                catch (SqlException ex)
                {
                    // 处理SQL异常（如连接失败、查询错误）
                    Console.WriteLine($"数据库错误：{ex.Message}");
                    throw; // 可选择重新抛出或包装异常
                }
                catch (Exception ex)
                {
                    // 处理其他异常
                    Console.WriteLine($"意外错误：{ex.Message}");
                    throw;
                }
            }
        }

        public async Task<PagedResult<Human_File>> SearchHumanFiles(
       string sqlserver,
       string firstKindName,
       string secondKindName,
       string thirdKindName,
       string jobCategoryName,
       string jobName,
       DateTime? startDate,
       DateTime? endDate,
       int pageNumber = 1,
       int pageSize = 2)
        {
            // 使用OFFSET/FETCH实现分页
            string sql = @"
SELECT * 
FROM (
    SELECT *,
        ROW_NUMBER() OVER (ORDER BY human_id) AS RowNum
    FROM human_file hf
    WHERE 1=1
    AND (@FirstKindName IS NULL OR hf.first_kind_name = @FirstKindName)
    AND (@SecondKindName IS NULL OR hf.second_kind_name = @SecondKindName)
    AND (@ThirdKindName IS NULL OR hf.third_kind_name = @ThirdKindName)
    AND (@JobCategoryName IS NULL OR hf.human_major_kind_name = @JobCategoryName)
    AND (@JobName IS NULL OR hf.hunma_major_name = @JobName)
    AND (@StartDate IS NULL OR hf.regist_time >= @StartDate)
    AND (@EndDate IS NULL OR hf.check_time <= @EndDate)
) AS SubQuery
WHERE RowNum BETWEEN (@PageNumber - 1) * @PageSize + 1 AND @PageNumber * @PageSize
ORDER BY RowNum";

            string countSql = @"
SELECT COUNT(*) 
FROM human_file hf
WHERE 1=1
AND (@FirstKindName IS NULL OR hf.first_kind_name = @FirstKindName)
AND (@SecondKindName IS NULL OR hf.second_kind_name = @SecondKindName)
AND (@ThirdKindName IS NULL OR hf.third_kind_name = @ThirdKindName)
AND (@JobCategoryName IS NULL OR hf.human_major_kind_name = @JobCategoryName)
AND (@JobName IS NULL OR hf.hunma_major_name = @JobName)
AND (@StartDate IS NULL OR hf.regist_time >= @StartDate)
AND (@EndDate IS NULL OR hf.check_time <= @EndDate)";

            using (var connection = new SqlConnection(sqlserver))
            {
                await connection.OpenAsync();

                var parameters = new DynamicParameters();
                //parameters.Add("@FirstKindName", firstKindName);
                //parameters.Add("@SecondKindName", secondKindName);
                //parameters.Add("@ThirdKindName", thirdKindName);
                //parameters.Add("@JobCategoryName", jobCategoryName);
                //parameters.Add("@JobName", jobName);



                parameters.Add("@FirstKindName", string.IsNullOrEmpty(firstKindName) ? null : firstKindName);
                parameters.Add("@SecondKindName", string.IsNullOrEmpty(secondKindName) ? null : secondKindName);
                parameters.Add("@ThirdKindName", string.IsNullOrEmpty(thirdKindName) ? null : thirdKindName);
                parameters.Add("@JobCategoryName", string.IsNullOrEmpty(jobCategoryName) ? null : jobCategoryName);
                parameters.Add("@JobName", string.IsNullOrEmpty(jobName) ? null : jobName);
                parameters.Add("@StartDate", startDate);
                parameters.Add("@EndDate", endDate);
                parameters.Add("@PageNumber", pageNumber);
                parameters.Add("@PageSize", pageSize);

                // 执行分页查询
                var items = await connection.QueryAsync<Human_File>(sql, parameters);

                // 获取总记录数
                var totalCount = await connection.ExecuteScalarAsync<int>(countSql, parameters);

                return new PagedResult<Human_File>
                {
                    Items = items.ToList(),
                    TotalCount = totalCount,
                    PageNumber = pageNumber,
                    PageSize = pageSize
                };
            }
        }


        public async Task<Human_File?> SelectHFIDAsync(int huf_id, string sqlserver)
        {
            Task<Human_File?> task = Task<Human_File>.Run(() =>
            {
                using (SqlConnection sqlConnection = new SqlConnection(sqlserver))
                {

                    string sql = $@"select * from [dbo].[human_file] where [huf_id]={huf_id}";

                    return sqlConnection.Query<Human_File>(sql).FirstOrDefault();
                }

            });

            return await task;
        }

        public async Task<int> Update1HFAsync(Human_File human_File, string sqlserver)
        {
            using (SqlConnection sqlConnection = new SqlConnection(sqlserver))
            {

                string sql = $@"update [dbo].[human_file] set [human_name]='{human_File.human_name}',[human_pro_designation]='{human_File.human_pro_designation}',[human_sex]='{human_File.human_sex}',[human_email]='{human_File.human_email}',[human_telephone]='{human_File.human_telephone}',[human_qq]='{human_File.human_qq}',[human_mobilephone]='{human_File.human_mobilephone}',
        [human_address]='{human_File.human_address}',[human_nationality]='{human_File.human_nationality}',[human_birthplace]='{human_File.human_birthplace}',[human_birthday]='{human_File.human_birthday}',[human_race]='{human_File.human_race}',[human_religion]='{human_File.human_religion}',[human_party]='{human_File.human_party}',[human_id_card]='{human_File.human_id_card}',[human_society_security_id]='{human_File.human_society_security_id}',
        [human_age]='{human_File.human_age}', [human_educated_degree]='{human_File.human_educated_degree}',[human_educated_years]='{human_File.human_educated_years}',[human_educated_major] ='{human_File.human_educated_major}',[salary_standard_name]='{human_File.salary_standard_name}',[human_bank]='{human_File.human_bank}',[human_account]='{human_File.human_account}',[human_histroy_records]='{human_File.human_histroy_records}',
        [human_family_membership]='{human_File.human_family_membership}',[human_postcode]='{human_File.human_postcode}',[human_speciality]='{human_File.human_speciality}',[human_hobby]='{human_File.human_hobby}',[check_time]='{human_File.check_time}',[human_picture]='{human_File.human_picture}',[remark]='{human_File.remark}', [salary_standard_id]='{human_File.salary_standard_id}' ,[check_status]='{human_File.check_status}',[attachment_name]='{human_File.attachment_name}',[checker]='{human_File.checker}'  where [huf_id]='{human_File.huf_id}' ";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }

        public async Task<int> UpdateBGHFAsync(Human_File human_File, string sqlserver)
        {
            using (SqlConnection sqlConnection = new SqlConnection(sqlserver))
            {

                string sql = $@"update [dbo].[human_file] set [human_name]='{human_File.human_name}',[human_pro_designation]='{human_File.human_pro_designation}',[human_sex]='{human_File.human_sex}',[human_email]='{human_File.human_email}',[human_telephone]='{human_File.human_telephone}',[human_qq]='{human_File.human_qq}',[human_mobilephone]='{human_File.human_mobilephone}',
                [human_address]='{human_File.human_address}',[human_nationality]='{human_File.human_nationality}',[human_birthplace]='{human_File.human_birthplace}',[human_birthday]='{human_File.human_birthday}',[human_race]='{human_File.human_race}',[human_religion]='{human_File.human_religion}',[human_party]='{human_File.human_party}',[human_id_card]='{human_File.human_id_card}',[human_society_security_id]='{human_File.human_society_security_id}',
                [human_age]='{human_File.human_age}', [human_educated_degree]='{human_File.human_educated_degree}',[human_educated_years]='{human_File.human_educated_years}',[human_educated_major] ='{human_File.human_educated_major}',[salary_standard_name]='{human_File.salary_standard_name}',[human_bank]='{human_File.human_bank}',[human_account]='{human_File.human_account}',[human_histroy_records]='{human_File.human_histroy_records}',
                [human_family_membership]='{human_File.human_family_membership}',[human_postcode]='{human_File.human_postcode}',[human_speciality]='{human_File.human_speciality}',[human_hobby]='{human_File.human_hobby}',[check_time]='{human_File.check_time}',[human_picture]='{human_File.human_picture}',[remark]='{human_File.remark}', [salary_standard_id]='{human_File.salary_standard_id}' ,[attachment_name]='{human_File.attachment_name}'   where [huf_id]='{human_File.huf_id}' ";
                return await sqlConnection.ExecuteAsync(sql);

            }

        }

        public async Task<int> UpdateHFAsync(Human_File human_File, string sqlserver, int id)
        {

            using (SqlConnection sqlConnection = new SqlConnection(sqlserver))
            {

                string sql = $@"update [dbo].[human_file] set [human_name]='{human_File.human_name}',[human_pro_designation]='{human_File.human_pro_designation}',[human_sex]='{human_File.human_sex}',[human_email]='{human_File.human_email}',[human_telephone]='{human_File.human_telephone}',[human_qq]='{human_File.human_qq}',[human_mobilephone]='{human_File.human_mobilephone}',
        [human_address]='{human_File.human_address}',[human_nationality]='{human_File.human_nationality}',[human_birthplace]='{human_File.human_birthplace}',[human_birthday]='{human_File.human_birthday}',[human_race]='{human_File.human_race}',[human_religion]='{human_File.human_religion}',[human_party]='{human_File.human_party}',[human_id_card]='{human_File.human_id_card}',[human_society_security_id]='{human_File.human_society_security_id}',
        [human_age]='{human_File.human_age}', [human_educated_degree]='{human_File.human_educated_degree}',[human_educated_years]='{human_File.human_educated_years}',[human_educated_major] ='{human_File.human_educated_major}',[salary_standard_name]='{human_File.salary_standard_name}',[human_bank]='{human_File.human_bank}',[human_account]='{human_File.human_account}',[human_histroy_records]='{human_File.human_histroy_records}',
        [human_family_membership]='{human_File.human_family_membership}',[human_postcode]='{human_File.human_postcode}',[human_speciality]='{human_File.human_speciality}',[human_hobby]='{human_File.human_hobby}',[check_time]='{human_File.check_time}',[human_picture]='{human_File.human_picture}',[remark]='{human_File.remark}' where [huf_id]='{id}' ";
                return await sqlConnection.ExecuteAsync(sql);
            }
              
        }

        public async Task<int> UpdateHFBTGAsync(Human_File human_File, string sqlserver)
        {
            using (SqlConnection sqlConnection = new SqlConnection(sqlserver))
            {

                string sql = $@" update [dbo].[human_file]  set [check_status]='{human_File.check_status}' ,[checker]='{human_File.checker}',[check_time]='{human_File.check_time}' where [huf_id]='{human_File.huf_id}' ";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }


    }
}
